﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DAO;
using DTO;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelExample.logic;

namespace GUI
{
    public partial class BaoCaoLuotMuonTrogThang : UserControl
    {
        public BaoCaoLuotMuonTrogThang()
        {
            InitializeComponent();
        }
        int tongsoluotmuon = 0;
        int currMonth = 0;
        private void AddRows()
        {
            List<LoaiSachDTO> dsloaisach = LoaiSachDAO.TimTatCaLoaiSach1();
            float rate = 0f;
            int sosach = 0;
            int soluotmuon = 0;
            tongsoluotmuon = 0;
            SqlConnection connect = new SqlConnection(SqlDataAccessHelper.ReadConnectionString("ConnectionString.xml"));
            connect.Open();
            SqlCommand command = connect.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.UpdatedRowSource = UpdateRowSource.OutputParameters;
            // Out-Parameter: SoSach
            SqlParameter outparam = new SqlParameter();
            outparam.ParameterName = "@SoSach";
            outparam.DbType = DbType.Int32;
            outparam.Direction = ParameterDirection.Output;

            for (int i = 0; i < dsloaisach.Count; i++)
            {
                try
                {
                    // Tim ra tong so sach theo the loai
                    command.Parameters.Clear();
                    command.Parameters.Add(new SqlParameter("@MaLoaiSach", dsloaisach[i].MaLoaiSach));
                    command.Parameters.Add(outparam);
                    command.CommandText = "TinhSoSachCuaLoaiSach";
                    command.ExecuteNonQuery();
                    sosach = SqlDataAccessHelper.ParseInt(command.Parameters["@SoSach"].Value.ToString());
                    // Tim ra tong so sach theo the loai
                    // Clear sqlParams list
                    command.Parameters.Clear();
                    command.Parameters.Add(new SqlParameter("@Thang", currMonth));
                    command.Parameters.Add(new SqlParameter("@MaLoaiSach", dsloaisach[i].MaLoaiSach));
                    command.Parameters.Add(outparam);
                    command.CommandText = "TimSoSachMuonTrongThangCuaLoaiSach";
                    command.ExecuteNonQuery();
                    soluotmuon = SqlDataAccessHelper.ParseInt(command.Parameters["@SoSach"].Value.ToString());
                    tongsoluotmuon += soluotmuon;
                    // Tim ra ti le
                    rate = (sosach == 0) ? 0 : (float)soluotmuon / sosach;
                    // Tao mang object chua thong tin theo loai sach
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    object[] row = { i + 1, dsloaisach[i].TenLoaiSach, soluotmuon, rate };
                    dgvReport.Rows.Add(row);
                }
            }
            lblTongluotmuon.Text = tongsoluotmuon.ToString();
        }

        private void BaoCaoLuotMuonTrogThang_Load(object sender, EventArgs e)
        {
            monthCmb.SelectedItem = DateTime.Now.Month.ToString();
            lblCurrDay.Text = DateTime.Now.ToString();
        }

        private void monthCmb_SelectedIndexChanged(object sender, EventArgs e)
        {
            dgvReport.Rows.Clear();
            int.TryParse(monthCmb.SelectedItem.ToString(), out currMonth);
            AddRows();
        }

        private void ExportExcelBtn_Click(object sender, EventArgs e)
        {
            Congdongcviet();
        }

        private void Congdongcviet()
        {
            SaveFileDialog save = new SaveFileDialog();
            save.Filter = "Excel file *.xls|*.xls";
            if (save.ShowDialog() == DialogResult.OK)
            {
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Excel.ApplicationClass();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i;
                int j;
                xlWorkSheet.Cells[1, 1] = label1.Text;
                xlWorkSheet.Cells[2, 1] = "THÁNG: ";
                xlWorkSheet.Cells[2, 2] = currMonth;
                xlWorkSheet.Cells[3, 1] = "NGÀY TẠO: ";
                xlWorkSheet.Cells[3, 2] = DateTime.Now;
                for (i = 5; i <= dgvReport.RowCount + 4; i++)
                {
                    for (j = 0; j <= dgvReport.ColumnCount - 1; j++)
                    {
                        DataGridViewCell cell = dgvReport[j, i - 5];
                        xlWorkSheet.Cells[5, j + 1] = dgvReport.Columns[j].HeaderText;
                        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                    }
                }
                xlWorkSheet.Cells[i + 2, 1] = "TỔNG SỐ LƯỢT MƯỢN:";
                xlWorkSheet.Cells[i + 3, 1] = lblTongluotmuon.Text;
                xlWorkBook.SaveAs(save.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                MessageBox.Show("Excel file created successfully !");
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            }
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (System.Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception occured while releasing object: " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        private void outBtn_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}
